#include "sql.h"//数据库部分
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <time.h>

static sqlite3 *db_handle = NULL;

//初始化
int sql_init_db(const char *db_path) {
    if (db_handle != NULL) {
        return 0; // 已经初始化
    }

    int rc = sqlite3_open(db_path, &db_handle);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "无法打开数据库: %s\n", sqlite3_errmsg(db_handle));
        return -1;
    }

    // 创建数据表
    const char *sql = 
        "CREATE TABLE IF NOT EXISTS sensor_data ("
        "id INTEGER PRIMARY KEY AUTOINCREMENT,"
        "timestamp DATETIME NOT NULL,"
        "temperature REAL NOT NULL,"
        "humidity REAL NOT NULL,"
        "sensor_id TEXT NOT NULL,"
        "uploaded INTEGER DEFAULT 0,"
        "CHECK (temperature BETWEEN -40 AND 85),"
        "CHECK (humidity BETWEEN 0 AND 100)"
        ");";
    
    char *err_msg = NULL;
    rc = sqlite3_exec(db_handle, sql, NULL, NULL, &err_msg);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "创建表失败: %s\n", err_msg);
        sqlite3_free(err_msg);
        sqlite3_close(db_handle);
        db_handle = NULL;
        return -1;
    }

    // 创建索引提高查询性能
    sql = "CREATE INDEX IF NOT EXISTS idx_uploaded ON sensor_data(uploaded);";
    sqlite3_exec(db_handle, sql, NULL, NULL, NULL);

    return 0;
}

//关闭数据库 
int sql_close_db(void) {
    if (db_handle) {
        sqlite3_close(db_handle);
        db_handle = NULL;
    }
    return 0;
}

// 传入传感器数据 
int sql_insert_data(const SensorData *data) {
    if (!db_handle) return -1;

    const char *sql = 
        "INSERT INTO sensor_data (timestamp, temperature, humidity, sensor_id) "
        "VALUES(?, ?, ?, ?);";
    
    sqlite3_stmt *stmt;
    int rc = sqlite3_prepare_v2(db_handle, sql, -1, &stmt, NULL);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "准备语句失败: %s\n", sqlite3_errmsg(db_handle));
        return -1;
    }

    sqlite3_bind_text(stmt, 1, data->timestamp, -1, SQLITE_STATIC);
    sqlite3_bind_double(stmt, 2, data->temperature);
    sqlite3_bind_double(stmt, 3, data->humidity);
    sqlite3_bind_text(stmt, 4, data->sensor_id, -1, SQLITE_STATIC);

    rc = sqlite3_step(stmt);
    sqlite3_finalize(stmt);

    if (rc != SQLITE_DONE) {
        fprintf(stderr, "插入数据失败: %s\n", sqlite3_errmsg(db_handle));
        return -1;
    }

    return sqlite3_last_insert_rowid(db_handle);
}

//获取未上传的数据 
int sql_get_unuploaded(SensorData *results, int max_count) {
    if (!db_handle || max_count <= 0) return -1;

    const char *sql = 
        "SELECT id, timestamp, temperature, humidity, sensor_id "
        "FROM sensor_data "
        "WHERE uploaded = 0 "
        "ORDER BY timestamp ASC "
        "LIMIT ?;";
    
    sqlite3_stmt *stmt;
    int rc = sqlite3_prepare_v2(db_handle, sql, -1, &stmt, NULL);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "准备查询失败: %s\n", sqlite3_errmsg(db_handle));
        return -1;
    }

    sqlite3_bind_int(stmt, 1, max_count);
    
    int count = 0;
    while ((rc = sqlite3_step(stmt)) == SQLITE_ROW && count < max_count) {
        results[count].id = sqlite3_column_int(stmt, 0);
        strncpy(results[count].timestamp, (const char*)sqlite3_column_text(stmt, 1), 20);
        results[count].temperature = sqlite3_column_double(stmt, 2);
        results[count].humidity = sqlite3_column_double(stmt, 3);
        strncpy(results[count].sensor_id, (const char*)sqlite3_column_text(stmt, 4), 32);
        results[count].uploaded = false;
        count++;
    }

    sqlite3_finalize(stmt);
    return count;
}

//已上传 
int sql_mark_uploaded(int record_id) {
    if (!db_handle) return -1;

    const char *sql = "UPDATE sensor_data SET uploaded = 1 WHERE id = ?;";
    sqlite3_stmt *stmt;
    
    int rc = sqlite3_prepare_v2(db_handle, sql, -1, &stmt, NULL);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "准备更新语句失败: %s\n", sqlite3_errmsg(db_handle));
        return -1;
    }

    sqlite3_bind_int(stmt, 1, record_id);
    rc = sqlite3_step(stmt);
    sqlite3_finalize(stmt);

    return (rc == SQLITE_DONE) ? 0 : -1;
}

//删除部分
int sql_delete_old_data(int days_old) {
    if (!db_handle) return -1;

    char sql[256];
    snprintf(sql, sizeof(sql), 
        "DELETE FROM sensor_data "
        "WHERE uploaded = 1 AND "
        "julianday('now') - julianday(timestamp) > %d;", 
        days_old);

    char *err_msg = NULL;
    int rc = sqlite3_exec(db_handle, sql, NULL, NULL, &err_msg);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "删除旧数据失败: %s\n", err_msg);
        sqlite3_free(err_msg);
        return -1;
    }

    return sqlite3_changes(db_handle);
}